package microcredit.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.sql.DataSource;

import microcredit.Version;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ddlutils.DatabaseOperationException;
import org.apache.ddlutils.DdlUtilsException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.apache.derby.jdbc.ClientDataSource;
import org.apache.derby.jdbc.EmbeddedDataSource;
public class Factory {

	private static final Log LOGGER = LogFactory.getLog(Factory.class);
	
	private static final String UNIT_NAME = "microcredit";
	
	private static EntityManagerFactory factory;
	
	private static final String PROPERTY_DRIVER = "eclipselink.jdbc.driver";
	private static final String PROPERTY_URL = "eclipselink.jdbc.url";
	private static final String PROPERTY_USER = "eclipselink.jdbc.user";
	private static final String PROPERTY_PASSWORD = "eclipselink.jdbc.password";
	
	private static final String EMBEDDED_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
	private static final String CLIENT_DRIVER = "org.apache.derby.jdbc.ClientDriver";
	
	private static final String PROPERTY_DDL_GENERATION = "eclipselink.ddl-generation";
	private static final String DDL_GENERATION_NONE = "none";
	private static final String DDL_GENERATION_CREATE_TABLES = "create-tables";
	private static final String PROPERTY_DDL_OUTPUT_MODE = "eclipselink.ddl-generation.output-mode";
	private static final String OUTPUT_MODE_DATABASE = "database";
	
	public static void close(boolean shutdown) {
		if (factory != null) {
			factory.close();
			factory = null;
			if (shutdown) {
				try {
					LOGGER.info("Performing shutdown");
					Connection c = createShutdownConnection();
					c.close();
					LOGGER.info("Performed shutdown");
				} catch (Exception e) {
					if (e instanceof SQLException) {
						if (((SQLException) e).getErrorCode() == 45000) {
							// ignore that exception -> means that shutdown was successful.
							return;
						}
					}
					LOGGER.error(e.getMessage(), e);
				}
			}
		}
	}
	
	public static void open() throws Exception {
		factory = createDefaultFactory();
	}
	
	public static EntityManagerFactory getFactory() {
		return factory;
	}
	
	public static String getEclipseLinkProperties() {
		String eclipselink = System.getProperty("eclipselink");
		if (eclipselink == null) {
			return "eclipselink-local.properties";
		} else {
			return eclipselink;
		}
	}
	
	private static boolean isDBUpToDate() throws Exception {
		Connection connection = createConnection(loadProperties());
		try {
			String versionString = readVersion(connection);
			if (versionString == null || versionString.equals("")) {
				return false;
			}
			return Version.VERSION.equals(versionString);
		} finally {
			connection.close();
		}
	}
	
	private static Properties loadProperties() throws FileNotFoundException, IOException {
		String eclipseLink = getEclipseLinkProperties();
		Properties properties = new Properties();
		properties.load(new FileInputStream(eclipseLink));
		return properties;
	}
	
	public static String readVersion(Connection connection) throws Exception {
		String versionString;
		try {
			PreparedStatement statement = connection.prepareStatement("select version as version from version where id=0");
			ResultSet result = statement.executeQuery();
			if (!result.next()) {
				return null;
			}
			versionString = result.getString("version");
			if (versionString == null) {
				return "";
			}
			result.close();
		} catch (Exception e) {
			return null;
		}
		return versionString;
	}
	
	private static void updateVersion(String version) throws Exception {
		Connection connection = createConnection(loadProperties());
		try {
			String currentVersion = readVersion(connection);
			PreparedStatement statement;
			if (currentVersion == null)  {
				statement = connection.prepareStatement("insert into version (id, version) values (0, ?)");
			} else {
				statement = connection.prepareStatement("update version set version=? where id=0");
			}
			statement.setString(1, version);
			int result = statement.executeUpdate();
			if (result != 1) {
				throw new SQLException("Could not update version");
			}
			connection.commit();
		} finally {
			connection.rollback();
			connection.close();
		}
	}
	
	public static Connection createDefaultConnection() throws Exception {
		return createConnection(loadProperties());
	}
	
	public static Connection createShutdownConnection() throws Exception {
		Properties properties = loadProperties();
		String url = properties.getProperty(PROPERTY_URL);
		if (url.indexOf(';') != -1)
			url = url.substring(0, url.lastIndexOf(';'));
		String dbName = url.substring(url.lastIndexOf('/') + 1);
//		url = url.substring(0, url.lastIndexOf(';'));
//		properties.setProperty(PROPERTY_URL, 
//				url + ";createFrom=" +  path.getAbsolutePath());
		properties.setProperty(PROPERTY_URL, 
				url + ";shutdown=true");
		return createConnection(properties);
	}
	
	public static Connection createRestoreConnection(File path) throws Exception {
		Properties properties = loadProperties();
		String url = properties.getProperty(PROPERTY_URL);
		if (url.indexOf(';') != -1)
			url = url.substring(0, url.lastIndexOf(';'));
		String dbName = url.substring(url.lastIndexOf('/') + 1);
//		url = url.substring(0, url.lastIndexOf(';'));
//		properties.setProperty(PROPERTY_URL, 
//				url + ";createFrom=" +  path.getAbsolutePath());
		properties.setProperty(PROPERTY_URL, 
				url + ";restoreFrom=" +  path.getPath() + "/" + dbName );
		
		return createConnection(properties);
	}
	
	public static Platform createDefaultPlatform() throws FileNotFoundException, IOException {
		return PlatformFactory.createNewPlatformInstance(createProductionDataSource());
	}
	
	private static Connection createConnection(Properties properties) throws Exception {
		
		String driver = (String)properties.get(PROPERTY_DRIVER);
		String url = (String)properties.get(PROPERTY_URL);
		String user = (String)properties.get(PROPERTY_USER);
		String password = (String)properties.get(PROPERTY_PASSWORD);
		
		// load driver
		Class.forName(driver);
		return DriverManager.getConnection(url, user, password);
	}
	
	public static void initFactoryNew() throws Exception {
		if (isDBUpToDate()) {
			LOGGER.info("Database is up to date!");
			// if up to date just start up the database
		} else {
			LOGGER.info("Database needs update!");
			
			upgradeDatabase();
			
			updateVersion(Version.VERSION);
		}
	
		factory = createDefaultFactory();
	}
	
	private static EntityManagerFactory createDefaultFactory() throws FileNotFoundException, IOException {
		Properties properties = loadProperties();
		properties.put(PROPERTY_DDL_GENERATION, DDL_GENERATION_NONE);
		return Persistence.createEntityManagerFactory(UNIT_NAME, properties);
	}
	
	private static final String TEST_DB = "db/test";
	private static final String TEST_DB_URL = "jdbc:derby:"+TEST_DB+";create=true";
	private static final String TEST_DB_DRIVER = EMBEDDED_DRIVER;
	private static final String TEST_DB_USER = "";
	private static final String TEST_DB_PASSWORD = "";
	
	private static void upgradeDatabase() throws FileNotFoundException, IOException, DatabaseOperationException, SQLException {
		deleteDB(new File(TEST_DB));
		
		Properties properties = loadProperties();
		properties.put(PROPERTY_DRIVER, TEST_DB_DRIVER);
		properties.put(PROPERTY_URL, TEST_DB_URL);
		properties.put(PROPERTY_USER, TEST_DB_USER);
		properties.put(PROPERTY_PASSWORD, TEST_DB_PASSWORD);
		
		properties.put(PROPERTY_DDL_GENERATION, DDL_GENERATION_CREATE_TABLES);
		properties.put(PROPERTY_DDL_OUTPUT_MODE, OUTPUT_MODE_DATABASE);
		
		factory = Persistence.createEntityManagerFactory(UNIT_NAME, properties);
		factory.createEntityManager().close();
		factory.close();
		
		// read model from test database
		EmbeddedDataSource testDatasource = new EmbeddedDataSource();
		testDatasource.setCreateDatabase("create");
		testDatasource.setDatabaseName(TEST_DB);
		Platform p1 = PlatformFactory.createNewPlatformInstance(testDatasource);
		Database testdb = p1.readModelFromDatabase("APP");
		
		// create connection to production system
		Platform p2 = PlatformFactory.createNewPlatformInstance(createProductionDataSource());
		
		LOGGER.info("Performing alter tables");
		p2.alterTables(testdb, false);
		
		// close connections
		p2.borrowConnection().close();
		p1.borrowConnection().close();
		
		deleteDB(new File(TEST_DB));
	}
	
	private static DataSource createProductionDataSource() throws FileNotFoundException, IOException {
		Properties properties = loadProperties();
		String driver = (String)properties.get(PROPERTY_DRIVER);
		if (driver.equals(EMBEDDED_DRIVER)) {
			EmbeddedDataSource prodDatasource = new EmbeddedDataSource();
			prodDatasource.setCreateDatabase("create");
			prodDatasource.setDatabaseName("db/db");
			return prodDatasource;
		} else if (driver.equals(CLIENT_DRIVER)) {
			ClientDataSource clientDataSource = new ClientDataSource();
			clientDataSource.setDatabaseName("microcredit");
			clientDataSource.setServerName("localhost");
			clientDataSource.setUser("root");
			clientDataSource.setPassword("root");
			return clientDataSource; 
		} else {
			throw new UnsupportedOperationException("Unsupported driver");
		}
	}
	
	public static void initFactory() throws DdlUtilsException, FileNotFoundException, IOException, SQLException {
		File f = new File("db/db/version.info");
		if (f.exists()) {
			String line = new BufferedReader(new InputStreamReader(new FileInputStream(f))).readLine();
			if (line.equals(microcredit.Version.VERSION)) {
				System.out.println("Database up to date!");
				
				Properties properties = new Properties();
				properties.load(new FileInputStream("./eclipselink.properties"));
				properties.put("eclipselink.ddl-generation", "none");
				factory = Persistence.createEntityManagerFactory(UNIT_NAME, properties);
				// nothing to do
				return;
			}
		} 
		
		System.out.println("DB Needs update");
		mergeDBs();
		
		if (!f.exists()) {
			f.createNewFile();
		}
		FileOutputStream stream = new FileOutputStream(f);
		stream.write(microcredit.Version.VERSION.getBytes());
		stream.close();
	}
	
	private static void mergeDBs() throws FileNotFoundException, IOException, DdlUtilsException, SQLException {
		deleteDB(new File("./db/test"));
		Properties properties = new Properties();
		
		properties.load(new FileInputStream("./eclipselink.properties"));
		
		String originalURL = properties.getProperty("eclipselink.jdbc.url");
		String testURL = "jdbc:derby:db/test;create=true";
		properties.put("eclipselink.jdbc.url", testURL);
		
		factory = Persistence.createEntityManagerFactory(UNIT_NAME, properties);
		factory.createEntityManager().close();
		factory.close();
		
		EmbeddedDataSource testDatasource = new EmbeddedDataSource();
		testDatasource.setCreateDatabase("create");
		testDatasource.setDatabaseName("db/test");
		Platform p1 = PlatformFactory.createNewPlatformInstance(testDatasource);
		
		Database testdb = p1.readModelFromDatabase("APP");
		
		EmbeddedDataSource prodDatasource = new EmbeddedDataSource();
		prodDatasource.setCreateDatabase("create");
		prodDatasource.setDatabaseName("db/db");
		Platform p2 = PlatformFactory.createNewPlatformInstance(prodDatasource);
		
		System.out.println("Performing alter tables");
		p2.alterTables(testdb, true);
		
		p2.borrowConnection().close();
		p1.borrowConnection().close();
		
		properties.put("eclipselink.jdbc.url", originalURL);
		properties.put("eclipselink.ddl-generation", "none");
		factory = Persistence.createEntityManagerFactory(UNIT_NAME, properties);
		
		deleteDB(new File("./db/test"));
	}
	
	private static void deleteDB(File f) {
		if (f.exists()) {
			if (f.isDirectory()) {
				File[] files = f.listFiles();
				for (File file : files) {
					deleteDB(file);
				}
			}
			f.delete();
		}
	}
	
	public static void main(String[] args) throws DdlUtilsException, FileNotFoundException, IOException, SQLException {
		mergeDBs();
	}

	public static IImportExport createImportExport() {
		return new ImportExportDefault();
	}
	
}
